IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetHeadDepartmentByMasterDepartmentLevel')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetHeadDepartmentByMasterDepartmentLevel
GO

/****** Object:  StoredProcedure [dbo].[USP_GetHeadDepartmentByMasterDepartmentLevel]    Script Date: 10/20/2010 15:51:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AKE
-- Create date: 17:08 20/9/2010
-- exec[USP_GetHeadDepartmentByMasterDepartmentLevel] 3
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetHeadDepartmentByMasterDepartmentLevel]
	-- Add the parameters for the stored procedure here
	@master_department_code int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare @master_department_level as int
	
	select @master_department_level = master_department_level
	from master_department 
	where master_department_code = @master_department_code
	
	select		
			stp.department_code,
			[department_name] = stp.department_name,--dbo.UFN_GetDepartmentHirachyByDepartmentCode(stp.department_code),
			stp.department_parent ,
			mas.master_department_level
	from	master_department as mas
	join	stp_department as stp on mas.master_department_code = stp.master_department_code
	left join stp_department as p_stp on stp.department_parent = p_stp.department_code
	where master_department_level >= @master_department_level 
		and stp.department_code > 0
	order by mas.master_department_level


END



GO
